﻿using SNMP;
using System;
using System.Data.SqlServerCe;

namespace SNMP_Agent
{
    public class DBOperate
    {
        private const string connectionString = @"Data Source=|DataDirectory|\MIB.sdf";
        private const string sqlSelect1 = "SELECT * FROM [VirtualDevice] WHERE [oid]=@oid";
        private const string sqlSelect2 = "SELECT * FROM [VirtualDevice] WHERE [index]=@index";
        private const string sqlSelect3 = "SELECT * FROM [VirtualDevice]";
        private SqlCeConnection connection;
        private SqlCeCommand command;

        /// <summary>
        /// 
        /// </summary>
        public DBOperate()
        {
            this.connection = new SqlCeConnection(connectionString);
            this.command = new SqlCeCommand();
            this.command.Connection = this.connection;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="oid"></param>
        /// <returns></returns>
        public Value FindValue(string oid)
        {
            return this.FindValue(new OID(oid));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="oid"></param>
        /// <returns></returns>
        public Value FindValue(OID oid)
        {
            MIB.VirtualDeviceDataTable dt = this.Select(oid);
            foreach (MIB.VirtualDeviceRow row in dt.Rows)
            {
                return DBOperate.Converter(row);
            }
            return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="oid"></param>
        /// <returns></returns>
        public MIB.VirtualDeviceDataTable Select(string oid)
        {
            return this.Select(new OID(oid));
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="oid"></param>
        /// <returns></returns>
        public MIB.VirtualDeviceDataTable Select(OID oid)
        {
            try
            {
                this.command.CommandText = DBOperate.sqlSelect1;
                this.command.Parameters.Clear();
                this.command.Parameters.Add(new SqlCeParameter("@oid", oid.ToString()));
                SqlCeDataAdapter adapter = new SqlCeDataAdapter(command);
                MIB.VirtualDeviceDataTable dt = new MIB.VirtualDeviceDataTable();
                adapter.Fill(dt);
                return dt;
            }
            catch
            {
                throw;
            }
        }

        public MIB.VirtualDeviceDataTable Select(int index)
        {
            try
            {
                this.command.CommandText = DBOperate.sqlSelect2;
                this.command.Parameters.Clear();
                this.command.Parameters.Add(new SqlCeParameter("@index", index));
                SqlCeDataAdapter adapter = new SqlCeDataAdapter(command);
                MIB.VirtualDeviceDataTable dt = new MIB.VirtualDeviceDataTable();
                adapter.Fill(dt);
                return dt;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <returns></returns>
        public MIB.VirtualDeviceDataTable SelectAll()
        {
            try
            {
                this.command.CommandText = DBOperate.sqlSelect3;
                this.command.Parameters.Clear();
                SqlCeDataAdapter adapter = new SqlCeDataAdapter(command);
                MIB.VirtualDeviceDataTable dt = new MIB.VirtualDeviceDataTable();
                adapter.Fill(dt);
                return dt;
            }
            catch
            {
                throw;
            }
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="oid"></param>
        /// <param name="nextoid"></param>
        /// <returns></returns>
        public Value FineNextValue(string oid, out OID nextoid)
        {
            return this.FineNextValue(new OID(oid), out nextoid);
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="oid"></param>
        /// <param name="nextoid"></param>
        /// <returns></returns>
        public Value FineNextValue(OID oid, out OID nextoid)
        {
            nextoid = new OID();
            MIB.VirtualDeviceDataTable dt = this.Select(oid);
            int nextInde = 0;
            foreach (MIB.VirtualDeviceRow row in dt.Rows)
            {
                nextInde = row.index + 1;
            }

            dt = this.Select(nextInde);
            foreach (MIB.VirtualDeviceRow row in dt.Rows)
            {
                nextoid = new OID(row.oid);
                return DBOperate.Converter(row);
            }
            return null;
        }

        /// <summary>
        /// 
        /// </summary>
        /// <param name="row"></param>
        /// <returns></returns>
        public static Value Converter(MIB.VirtualDeviceRow row)
        {
            SNMP.Value.Syntax syntax = (SNMP.Value.Syntax)System.Enum.Parse(typeof(SNMP.Value.Syntax), row.syntax);
            object value = row.value;
            return new Value(syntax, value);
        }
    }
}
